package com.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.entity.Customer;
import com.entity.Manager;
import com.entity.Member;

public class MemberDAO {

	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@210.125.212.171:1521:xe";
	String userid = "beer";
	String userpasswd = "test";

	public MemberDAO() {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public int insert(String id, String passwd, String name, String address,
			String phone, String email) {

		int result = 0;
		Connection con = null;
		PreparedStatement pstmt = null;

		try {
			con = DriverManager.getConnection(url, userid, userpasswd);
			String sql = "insert into member (mid, passwd, name, address, phone, email) values(?,?,?,?,?,?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			pstmt.setString(2, passwd);
			pstmt.setString(3, name);
			pstmt.setString(4, address);
			pstmt.setString(5, phone);
			pstmt.setString(6, email);

			int n = pstmt.executeUpdate();
			result = n;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	public int update(String id, String passwd, String name, String address,
			String phone, String email) {

		int result = 0;
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DriverManager.getConnection(url, userid, userpasswd);
			String sql = "update member set passwd = ?, name = ?, address = ?, phone = ?, email = ? where mid = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, passwd);
			pstmt.setString(2, name);
			pstmt.setString(3, address);
			pstmt.setString(4, phone);
			pstmt.setString(5, email);
			pstmt.setString(6, id);

			int n = pstmt.executeUpdate();
			result = n;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	public int delete(String id) {
		int result = 0;
		Connection con = null;
		PreparedStatement pstmt = null;

		try {
			con = DriverManager.getConnection(url, userid, userpasswd);
			String sql = "delete from member where mid = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);

			int n = pstmt.executeUpdate();
			result = n;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			try {
				if (pstmt != null) {
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	public ArrayList<Member> select() {

		ArrayList<Member> list = new ArrayList<Member>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			con = DriverManager.getConnection(url, userid, userpasswd);
			String sql = "select mid, name, address, phone, email from member";
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				String id = rs.getString("mid");
				// String passwd = rs.getString("passwd");
				String name = rs.getString("name");
				String address = rs.getString("address");
				String phone = rs.getString("phone");
				String email = rs.getString("email");
				Member member = new Member(id, "passwd", name, address, phone,
						email);
				list.add(member);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	public Member selectLogin(String mid, String mpasswd) {

		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Member mem = null;

		try {
			con = DriverManager.getConnection(url, userid, userpasswd);
			String sql = "select * from member where mid = ? and passwd = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, mid);
			pstmt.setString(2, mpasswd);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				String id = rs.getString("mid");
				String passwd = rs.getString("passwd");
				String name = rs.getString("name");
				String address = rs.getString("address");
				String phone = rs.getString("phone");
				String email = rs.getString("email");
				String type_yn = rs.getString("type_yn");

				if (type_yn.equals("Y")) {
					mem = new Manager(id, passwd, name, address, phone, email);
				} else {
					mem = new Customer(id, passwd, name, address, phone, email);
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return mem;
	}

	public Member selectByName(String name) {
		Member mem = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			con = DriverManager.getConnection(url, userid, userpasswd);
			String sql = "select * from member where name = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, name);

			rs = pstmt.executeQuery();

			while (rs.next()) {
				String id2 = rs.getString("mid");
				String passwd = rs.getString("passwd");
				String n_name = rs.getString("name");
				String address = rs.getString("address");
				String phone = rs.getString("phone");
				String email = rs.getString("email");
				String type = rs.getString("type_yn");

				if (type.equals("Y")) {

					mem = new Manager(id2, "", n_name, address, phone, email);
				} else {
					mem = new Customer(id2, "", n_name, address, phone, email);
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return mem;
	}
	
	
	public Member selectByID(String id){
		Member mem = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			con = DriverManager.getConnection(url, userid, userpasswd);
			String sql = "select * from member where mid = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);

			rs = pstmt.executeQuery();

			while (rs.next()) {
				String id2 = rs.getString("mid");
				String passwd = rs.getString("passwd");
				String n_name = rs.getString("name");
				String address = rs.getString("address");
				String phone = rs.getString("phone");
				String email = rs.getString("email");
				String type = rs.getString("type_yn");

				if (type.equals("Y")) {
					mem = new Manager(id2, "", n_name, address, phone, email);
				} else {
					mem = new Customer(id2, "", n_name, address, phone, email);
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return mem;
	}

}
